﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MDIApplication.Classes
{
    class HousekeepingStaffManagementDBManager
    {
        #region getStaffByStaffName
        public Staff getStaffByStaffName(string firstName, string lastName)
        {
            Staff s = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT First_Name,Last_Name,Staff_Account_ID,Contact_No,Date_of_Birth,Gender,Address,Staff_Position,Bank_Account_No,Bank_Account_Type FROM Staff WHERE First_Name=@firstName and Last_Name=@lastName ";
                comm.Parameters.AddWithValue("@firstName", firstName);
                comm.Parameters.AddWithValue("@lastName", lastName);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    s = new Staff();
                    s.firstName = (string)dr["First_Name"];
                    s.lastName = (string)dr["Last_Name"];
                    s.staffId = (string)dr["Staff_Account_ID"];
                    s.contactNo = Convert.ToInt32(dr["Contact_No"]); 
                    s.dob = Convert.ToDateTime(dr["Date_of_Birth"]);
                    s.gender = (string)dr["Gender"];
                    s.address = (string)dr["Address"];
                    s.staffPosition = (string)dr["Staff_Position"];
                    s.acctType = (string)dr["Bank_Account_Type"];
                    s.acctNo = (string)dr["Bank_Account_No"];
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }

            return s;

        }
        #endregion getStaffByStaffName

        #region getAllRooms
        public List<Room> getAllRooms()
        {
            List<Room> roomList = new List<Room>();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT Room_Unit_No, Room_Level, Room_Type, Room_Status from Room";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    Room ro = new Room();
                    ro.roomUnitNo = Convert.ToInt32(dr["Room_Unit_No"]);
                    ro.roomLevel = Convert.ToInt32(dr["Room_Level"]); ;
                    ro.roomType = (string)dr["Room_Type"]; ;
                    ro.roomStatus = (string)dr["Room_Status"]; ;
                    roomList.Add(ro);
                }
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return roomList;
        }
        #endregion getAllRooms

        #region UpdateStaff
        public int UpdateStaff(Staff staff)
        {
            int rowsupdated = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Staff SET Staff_Position=@staffPosition, First_Name=@firstName,Last_Name=@lastName,Contact_No=@contactNo,Address=@address,Bank_Account_No=@acctNo, Bank_Account_Type=@acctType, Date_of_Birth=@dob WHERE Staff_Account_ID=@staffId";

                comm.Parameters.AddWithValue("@staffId", staff.staffId);
                comm.Parameters.AddWithValue("@staffPosition", staff.staffPosition);
                comm.Parameters.AddWithValue("@firstName",staff.firstName);
                comm.Parameters.AddWithValue("@lastName", staff.lastName);
                comm.Parameters.AddWithValue("@gender", staff.gender);
                comm.Parameters.AddWithValue("@dob", staff.dob);
                comm.Parameters.AddWithValue("@contactNo", staff.contactNo);
                comm.Parameters.AddWithValue("@acctNo", staff.acctNo);
                comm.Parameters.AddWithValue("@acctType", staff.acctType);
                comm.Parameters.AddWithValue("@address", staff.address);

                rowsupdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }

            return rowsupdated;
        }
        #endregion UpdateStaff

        #region getHouseKeepStaffByStaffName
        public Staff getHouseKeepStaffByStaffName(string firstName, string lastName)
        {
            Staff s = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT Staff_Account_ID,Staff_Position,Duty_Type FROM Staff WHERE First_Name=@firstName and Last_Name=@lastName ";
                comm.Parameters.AddWithValue("@firstName", firstName);
                comm.Parameters.AddWithValue("@lastName", lastName);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    s = new Staff();
                    s.staffId = (string)dr["Staff_Account_ID"];
                    s.staffPosition = (string)dr["Staff_Position"];
                    s.dutyType = (string)dr["Duty_Type"];
                   
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }

            return s;

        }
        #endregion getHouseKeepStaffByStaffName

        #region UpdateHousekeepStaff
        public int UpdateHousekeepStaff(Staff staff)
        {
            int rowsupdated = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Staff SET Duty_Type=@dutyType WHERE Staff_Account_ID=@staffId";

               comm.Parameters.AddWithValue("@staffId", staff.staffId);
                comm.Parameters.AddWithValue("@dutyType", staff.dutyType);

                rowsupdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;
        }
        #endregion UpdateHousekeepStaff
    }
}
